package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import pos.utils.UIDGenerator;

import domain.Manifestacion;
import domain.Usuario;

public class JDBCManifestacionDAO implements IManifestacionDAO {

	@Override
	public void creaManifestacion(String autor, String fechaCelebracion,
			String ciudad, String tematica, String tipo) {
		// TODO Auto-generated method stub
		Connection conn = ConnectionManager.getInstance().checkOut();
		

		String sql = "INSERT INTO manifestacion (manifestacionOID,autor, fechaCelebracion, ciudad, meGusta, tematica, tipo) VALUES (?, ?, ?, ?, ?, ?, ?); ";

		String OIDManifestacion = UIDGenerator.getInstance().getKey();
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, OIDManifestacion);
			stmt.setString(2, autor);
			stmt.setString(3, fechaCelebracion);
			stmt.setString(4, ciudad);
			stmt.setInt(5, 0);
			stmt.setString(6, tematica);
			stmt.setString(7, tipo);

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
	}

	@Override
	public void actualizaVotacion(Integer idManifest) {
		// TODO Auto-generated method stub
		Manifestacion manifa = getManifestacion(idManifest);

		Integer meGusta = manifa.getMeGusta() + 1;

		Connection conn = ConnectionManager.getInstance().checkOut();

		String sql = "UPDATE manifestacion SET meGusta = ? WHERE idManifestacion = ?;";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, meGusta);
			stmt.setInt(2, idManifest);
			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

	}

	@Override
	public Manifestacion getManifestacion(Integer idManifest) {
		// TODO Auto-generated method stub

		Connection conn = ConnectionManager.getInstance().checkOut();

		Manifestacion manifa = new Manifestacion();
		PreparedStatement stmt = null;
		ResultSet result = null;

		String sql = "SELECT * FROM manifestacion WHERE idManifestacion = ?;";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idManifest);
			result = stmt.executeQuery();
			while (result.next()) {
				result.getInt("idManifestacion");
				manifa.setIdManifestacion(result.getInt("idManifestacion"));
				manifa.setManifestacionOID(result.getString("manifestacionOID"));
				manifa.setUsuario(result.getString("autor"));
				manifa.setFechaCreacion(result.getString("fechaCelebracion"));
				manifa.setCiudad(result.getString("ciudad"));
				manifa.setMeGusta(new Integer(result.getString("meGusta")));
				manifa.setTematica(result.getString("tematica"));
				manifa.setTipo(result.getString("tipo"));
				// FALTA TRATAMIENTO
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}

		return manifa;
	}

	@Override
	public void eliminaManifestacion(Integer idManifest) {
		// TODO Auto-generated method stub

		Connection conn = ConnectionManager.getInstance().checkOut();

		String sql = "DELETE FROM manifestacion WHERE idManifestacion = ?;";

		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, idManifest);

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
	}

	
	public List<String> interesesUsuario(String usuario) {
		// TODO Auto-generated method stub
		List<String> misPreferencias = new LinkedList<String>();
		Connection conn = ConnectionManager.getInstance().checkOut();

		//String sql = "SELECT * FROM preferencias WHERE (OIDUser = ? ) ";
		String sql = "SELECT p.* FROM preferencias p, user u WHERE u.dni = ? AND p.OIDUser = u.OIDUser";
		PreparedStatement stmt = null;
		ResultSet result = null;
		Usuario user = new Usuario();

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, usuario);

			result = stmt.executeQuery();
			result.next();
			user.setArtistica(new Integer(result.getString("Artistica")));
			user.setCultural(new Integer(result.getString("Cultural")));
			user.setSindical(new Integer(result.getString("Sindical")));
			user.setSocial(new Integer(result.getString("Sociales")));

			if (user.getArtistica().equals(1)) {
				misPreferencias.add("Artistica");
			}
			if (user.getCultural().equals(1)) {
				misPreferencias.add("Cultural");
			}
			if (user.getSindical().equals(1)) {
				misPreferencias.add("Sindical");
			}
			if (user.getSocial().equals(1)) {
				misPreferencias.add("Social");
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return misPreferencias;
	}

	@Override
	public List<Manifestacion> muestraManifestaciones() {
		// TODO Auto-generated method stub
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		List<Manifestacion> searchResults = new LinkedList<Manifestacion>();
		ResultSet result = null;

		try {
			String sql = "SELECT * FROM manifestacion;";
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				Manifestacion m = new Manifestacion();
				result.getInt("idManifestacion");
				m.setIdManifestacion(result.getInt("idManifestacion"));
				m.setManifestacionOID(result.getString("manifestacionOID"));
				m.setUsuario(result.getString("autor"));
				m.setFechaCreacion(result.getString("fechaCelebracion"));
				m.setCiudad(result.getString("ciudad"));
				m.setMeGusta(new Integer(result.getString("meGusta")));
				m.setTematica(result.getString("tematica"));
				m.setTipo(result.getString("tipo"));
				// FALTA TRATAMIENTO
				searchResults.add(m);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
		return searchResults;
	}

	public List<Manifestacion> getManifestacionesDe(String usuario) {
		// TODO Auto-generated method stub
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		List<Manifestacion> searchResults = new LinkedList<Manifestacion>();
		ResultSet result = null;

		try {
			String sql = "SELECT * FROM manifestacion WHERE autor = ?;";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, usuario);
			result = stmt.executeQuery();
			while (result.next()) {
				Manifestacion m = new Manifestacion();
				result.getInt("idManifestacion");
				m.setIdManifestacion(result.getInt("idManifestacion"));
				m.setManifestacionOID(result.getString("manifestacionOID"));
				m.setUsuario(result.getString("autor"));
				m.setFechaCreacion(result.getString("fechaCelebracion"));
				m.setCiudad(result.getString("ciudad"));
				m.setMeGusta(new Integer(result.getString("meGusta")));
				m.setTematica(result.getString("tematica"));
				m.setTipo(result.getString("tipo"));
				// FALTA TRATAMIENTO
				searchResults.add(m);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
		return searchResults;
	}

	@Override
	public List<Manifestacion> manifestacionesQueInteresan(String usuario) {
		// TODO Auto-generated method stub
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		List<Manifestacion> searchResults = new LinkedList<Manifestacion>();
		ResultSet result = null;

		List<String> intereses = interesesUsuario(usuario);
		
		for (int i = 0; i < intereses.size(); i++) {
			try {
				String sql = "SELECT * FROM manifestacion WHERE tipo = ?;";
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, intereses.get(i));
				result = stmt.executeQuery();
				while (result.next()) {
					Manifestacion m = new Manifestacion();
					result.getInt("idManifestacion");
					m.setIdManifestacion(result.getInt("idManifestacion"));
					m.setManifestacionOID(result.getString("manifestacionOID"));
					m.setUsuario(result.getString("autor"));
					m.setFechaCreacion(result.getString("fechaCelebracion"));
					m.setCiudad(result.getString("ciudad"));
					m.setMeGusta(new Integer(result.getString("meGusta")));
					m.setTematica(result.getString("tematica"));
					m.setTipo(result.getString("tipo"));
					// FALTA TRATAMIENTO
					searchResults.add(m);
				}

			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			}

		}

		ConnectionManager.getInstance().checkIn(conn);
		try {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
		}

		return searchResults;
	}
	
	public static List<Manifestacion> buscaManifestaciones(String tematica, String ciudad){
		List<Manifestacion> res = new LinkedList<Manifestacion>();
		try{
			Connection conn = ConnectionManager.getInstance().checkOut();
			PreparedStatement stmt = null;
			ResultSet result = null;
			String sql = "";
			
			if(tematica.equals("") && ciudad.equals(""))
				sql = "SELECT * FROM manifestacion";
			else if (!tematica.equals(""))
				sql = "SELECT * FROM manifestacion WHERE tematica LIKE %?%;";
			else if (!ciudad.equals(""))
				sql = "SELECT * FROM manifestacion WHERE ciudad LIKE %?%;";
			
			stmt = conn.prepareStatement(sql);
			if (!tematica.equals(""))
				stmt.setString(1, tematica);
			else if (!ciudad.equals(""))
				stmt.setString(1, ciudad);
			
			result = stmt.executeQuery();
			while (result.next()) {
				Manifestacion m = new Manifestacion();
				result.getInt("idManifestacion");
				m.setIdManifestacion(result.getInt("idManifestacion"));
				m.setManifestacionOID(result.getString("manifestacionOID"));
				m.setUsuario(result.getString("autor"));
				m.setFechaCreacion(result.getString("fechaCelebracion"));
				m.setCiudad(result.getString("ciudad"));
				m.setMeGusta(new Integer(result.getString("meGusta")));
				m.setTematica(result.getString("tematica"));
				//m.setTipo(result.getString("tipo"));
				res.add(m);
			}
			
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
			
		} catch(Exception e) {
			e.printStackTrace();
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + ((SQLException) e).getSQLState());
			System.out.println("ErrorCode: " + ((SQLException) e).getErrorCode());
		}
		return res;
	}

	public static Manifestacion getManifestacion2(Integer idManifest) {
		// TODO Auto-generated method stub

		Connection conn = ConnectionManager.getInstance().checkOut();

		Manifestacion manifa = new Manifestacion();
		PreparedStatement stmt = null;
		ResultSet result = null;

		String sql = "SELECT * FROM manifestacion WHERE idManifestacion = ?;";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idManifest);
			result = stmt.executeQuery();
			while (result.next()) {
				result.getInt("idManifestacion");
				manifa.setIdManifestacion(result.getInt("idManifestacion"));
				manifa.setManifestacionOID(result.getString("manifestacionOID"));
				manifa.setUsuario(result.getString("autor"));
				manifa.setFechaCreacion(result.getString("fechaCelebracion"));
				manifa.setCiudad(result.getString("ciudad"));
				manifa.setMeGusta(new Integer(result.getString("meGusta")));
				manifa.setTematica(result.getString("tematica"));
				manifa.setTipo(result.getString("tipo"));
				// FALTA TRATAMIENTO
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}

		return manifa;
	}
	
	// IComentarioDAO cdao;
	//
	// public JDBCManifestacionDAO() {
	// cdao = new JDBCComentarioDAO();
	// }
	//
	//
	// public List listarComentarios(Connection conn,String
	// manifestacionOID){
	//
	// List comentarios = new LinkedList();
	// String sql =
	// "SELECT * FROM Manifestacion WHERE (manifestacionOID = ? ) ";
	// PreparedStatement stmt = null;
	// // ResultSet result = null;
	//
	// try {
	// stmt = conn.prepareStatement(sql);
	// stmt.setString(1, manifestacionOID);
	//
	// //result = stmt.executeQuery();
	//
	// // while (result.next()) {
	// Manifestacion m = new Manifestacion();
	// // Comentario c = cdao.select(conn,
	// result.getString("comentarioOID"));
	// comentarios.add(m.getListaComentarios());
	// //}
	// } catch (SQLException e) {
	// System.out.println("Message: " + e.getMessage());
	// System.out.println("SQLState: " + e.getSQLState());
	// System.out.println("ErrorCode: " + e.getErrorCode());
	// } finally {
	// try {
	// // if (result != null) {
	// // result.close();
	// // }
	//
	// if (stmt != null) {
	// stmt.close();
	// }
	// } catch (SQLException e) {
	// }
	// }
	//
	// return comentarios;
	// }

}